import os
import re
import sys

import pandas as pd
from PyQt5.QtCore import Qt
from PyQt5.QtWidgets import QMessageBox, QFileDialog, QApplication, QMainWindow
from sqlalchemy import create_engine
from sqlalchemy import text as txt
from sqlalchemy.exc import SQLAlchemyError

from ui import *
from xjj_ffb import data_xjj_ffb
from xjj_fb import data_xjj_fb
from cne import cne
from model_ffb import model_data_ffb
from model_fb import model_fb_dict
from zc import model_zc


class Cleaner:  # 清理标准特征
    def __init__(self, df_input):
        self.df = df_input

    def clean(self, col, value, new_col=False):
        condition = self.df[col] != value
        if new_col:
            col_new = col + '_del'
            self.df[col_new] = self.df[col].where(condition, other='')
        else:
            self.df[col] = self.df[col].where(condition, other='')


def _in(row, x):
    return str(row[x]) in str(row['物料长文本描述'])


def tptp(x):
    return x.partition('\n')[0]


def assign_b_column(row):
    a_value = str(row['环境条件'])
    if 'F' in a_value:
        return 'F'
    elif 'F' not in a_value and ('W' in a_value):
        return 'W'
    else:
        return ''


def order_clear_df2(row):
    elements = row['订单'].split(',')
    a = row['环境条件']
    b = row['环境条件_2']
    if a in elements:
        elements.remove(a)
        elements.append(b)
    return ','.join(elements)


def jaccard_similarity(list1, list2):
    set1, set2 = set(list1), set(list2)
    return len(set1 & set2) / len(set1 | set2)


def sort_comma_elements_df1(cell):
    elements = cell.split(',')
    sorted_elements = sorted(elements)
    return ','.join(sorted_elements)


def sort_comma_elements_df2(row):
    elements = row['订单'].split(',')
    a = row['环境条件']
    b = row['环境条件_2']
    if a in elements:
        elements.remove(a)
        elements.append(b)
    sorted_elements = sorted(elements)
    return ','.join(sorted_elements)


def proof_grade(row):
    if 'BT' in str(row).upper():
        return 'BT'
    elif 'CT' in str(row).upper():
        return 'CT'
    else:
        return ''


class CheckCleaner:
    def __init__(self, df_input):
        self.df = df_input

    def check_and_clean(self, col, value):
        # 清除常规值
        col_del = f'{col}_del'
        condition = self.df[col] != value
        self.df[col_del] = self.df[col].fillna('').where(condition, other='')

    def check_in_description(self, col_name):
        new_col_name = f'{col_name}_bom'
        self.df[new_col_name] = self.df.apply(_in, args=(f'{col_name}_del',), axis=1).map(
            {True: '', False: f',{col_name}'})

    def check_normal_in_description(self, col_name):
        new_col_name = f'{col_name}_bom'
        self.df[new_col_name] = self.df.apply(_in, args=(f'{col_name}',), axis=1).map(
            {True: '', False: f',{col_name}'})

    def review(self, col: str, col_non_standard_configs: list, col_standard_config: str):
        pattern = "|".join(col_non_standard_configs)

        is_standard_or_empty = (
                (self.df[col] == col_standard_config) |
                (self.df[col] == '') |
                (self.df[col].isna())
        )

        feature_review_false = is_standard_or_empty & (
            self.df['物料长文本描述'].str.contains(pattern, na=False, regex=True))
        if feature_review_false.any():
            self.df.loc[feature_review_false, 'bom错误'] = self.df.loc[feature_review_false, 'bom错误'] + f',{col}'


class Window(QMainWindow, Ui_module):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.setWindowFlag(QtCore.Qt.FramelessWindowHint)
        self.setAttribute(QtCore.Qt.WA_TranslucentBackground)
        self.lineEdit.setText('mysql+pymysql://root:2001@localhost:3306/projectx')
        self.lineEdit_2.setText('数据库查询/命令语句')
        self.one.clicked.connect(self.handle_technical_prep)
        self.two.clicked.connect(self.handle_check_work_and_maintain_nameplate)
        self.three.clicked.connect(self.old_item)
        self.qx.clicked.connect(self.total_table)
        self.rk.clicked.connect(self.to_mysql)
        self.zxyj.clicked.connect(self.query)
        self.pushButton_3.clicked.connect(self.non_greedy_recommender)
        self.tj_tl.clicked.connect(self.greedy_recommender)
        self.drag_pos = None  # 用于记录鼠标按下时的偏移位置

    def display_dataframe(self, df, table_view):
        """将 DataFrame 显示在 QTableView 中"""
        # 创建一个 QStandardItemModel
        self.model = QtGui.QStandardItemModel()

        # 设置列数和列标题
        self.model.setColumnCount(len(df.columns))
        self.model.setHorizontalHeaderLabels(df.columns.tolist())

        # 填充数据
        for row in df.itertuples(index=False):
            items = [QtGui.QStandardItem(str(item)) for item in row]
            self.model.appendRow(items)

        # 将模型设置到 QTableView
        table_view.setModel(self.model)

    def mousePressEvent(self, event):
        if event.button() == Qt.LeftButton:
            self.drag_pos = event.globalPos() - self.frameGeometry().topLeft()
            event.accept()

    def mouseMoveEvent(self, event):
        if event.buttons() == Qt.LeftButton and self.drag_pos is not None:
            self.move(event.globalPos() - self.drag_pos)
            event.accept()

    def mouseReleaseEvent(self, event):
        self.drag_pos = None

    def handle_technical_prep(self):
        # 1. 打开文件对话框
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择Excel文件",
            "",
            "Excel Files (*.xls *.xlsx)"
        )
        if not file_path:
            return  # 用户取消了选择

        try:
            # 2. 读取 Excel 文件
            df = pd.read_excel(file_path, dtype=str)
            df.columns = [re.sub(r'[^\u4e00-\u9fa5]', '', col) for col in df.columns]

            df = df[['工作令号', '物料号码', '物料长文本描述',
                     '产品型号', '功率', '电压',
                     '频率', '安装方式', '绝缘等级', '防护等级', '出线方式',
                     '环境条件', '冷却方式', '行项目备注',
                     '防爆等级', '环境温度', '主接线盒位置及方向', '旋转方向', '轴承品牌', '海拔高度', '加热器',
                     '定子测温', '轴承测温']]  # 列筛选
            # 4. 去空白
            for i in df.columns:
                df[i] = df[i].str.strip()
            df['id'] = range(1, len(df) + 1)  # del col
            # 5. 映射表
            map_position = {
                '顶部右出线': '顶右',
                '顶部左出线': '顶左',
                '顶部前出线': '顶前',
                '顶部后出线': '顶后',
                '左侧下出线': '左下',
                '左侧上出线': '左上',
                '右侧后出线': '右后',
                '右侧上出线': '右上',
                '右侧下出线': '右下',
                '左侧后出线': '左后',
                '': '顶右'
            }
            df['主接线盒位置及方向_del'] = df['主接线盒位置及方向'].fillna('顶部右出线').map(map_position)
            df['主接线盒位置及方向_del'] = df['主接线盒位置及方向_del'].str.replace('顶右', '')
            df['旋转方向'] = df['旋转方向'].str.replace('标准', '双向')
            df['海拔高度_del'] = '海拔高度:' + df['海拔高度'] + 'm'
            df['海拔高度_del'] = df['海拔高度_del'].str.replace(' ', '')
            df['环境温度_del'] = '环境温度:' + df['环境温度'] + '℃'
            df['电压_del'] = df['电压'] + 'V'
            df['频率_del'] = df['频率'] + 'Hz'
            # 6. 清洗标准特征
            cleaner = Cleaner(df)
            cleaner.clean('电压_del', '380V')
            cleaner.clean('频率_del', '50Hz')
            cleaner.clean('绝缘等级', '155(F)', new_col=True)
            cleaner.clean('防护等级', 'IP55', new_col=True)
            cleaner.clean('环境条件', '户内', new_col=True)
            cleaner.clean('冷却方式', 'IC411', new_col=True)
            cleaner.clean('旋转方向', '双向', new_col=True)
            cleaner.clean('轴承品牌', '国内', new_col=True)
            cleaner.clean('加热器', '不带', new_col=True)
            cleaner.clean('定子测温', '不带', new_col=True)
            cleaner.clean('轴承测温', '不带', new_col=True)
            cleaner.clean('环境温度_del', '环境温度:-15～+40℃')
            cleaner.clean('海拔高度_del', '海拔高度:1000＜h≤1500m')
            cleaner.clean('海拔高度_del', '海拔高度:≤1000m')
            df['海拔高度_del'] = df['海拔高度_del'].str.replace('海拔高度:m', '')
            df['环境温度_del'] = df['环境温度_del'].str.replace('环境温度:℃', '')
            df = df.fillna('')
            # 7. 技术准备列生成
            df['技术准备'] = (
                    df['安装方式'] + ',' + df['电压_del'] + ',' + df['频率_del'] + ',' + df['绝缘等级_del'] + ',' +
                    df['防护等级_del'] + ',' + df['环境条件_del'] + ',' + df['冷却方式_del'] + ',' +
                    df['环境温度_del'] + ',' + df['旋转方向_del'] + ',' + df['轴承品牌_del'] + ',' +
                    df['海拔高度_del'] + ',' + df['主接线盒位置及方向_del'] + ',' + df['加热器_del'] + ',' + df[
                        '定子测温_del'] + ',' + df['轴承测温_del'] + ','
            )
            df = df.loc[:, ~df.columns.str.contains('_del')]
            # 8. 分类处理出线方式
            df1 = df[~df['产品型号'].str.contains('YB', na=False)].copy()
            df2 = df[df['产品型号'].str.contains('YB', na=False)].copy()
            df1['出线方式_1'] = df1['出线方式'].fillna('葛兰头')
            map_df1_gl = {
                '其他': '',
                '葛兰头': '',
                '钢布': '钢布',
                '橡套电缆(喇叭口)': '喇叭口',
                '标准': '',
                '钢布式葛兰': '钢布不锈钢格兰'
            }
            df1['出线方式_1'] = df1['出线方式_1'].map(map_df1_gl).fillna(df1['出线方式'])

            df2['出线方式_1'] = df2['出线方式'].fillna('橡套')
            map_df2_gl = {
                '钢布': '钢布',
                '橡套电缆(喇叭口)': '橡套',
                '标准': '橡套'
            }
            df2['出线方式_1'] = df2['出线方式_1'].map(map_df2_gl).fillna(df2['出线方式'])
            df = pd.concat([df1, df2], join='outer', ignore_index=True)
            # 9. 最终拼接
            df['技术准备'] = df['技术准备'] + df['出线方式_1']
            df['技术准备'] = df['技术准备'].str.replace(r',+', ',', regex=True)
            df['技术准备'] = df['技术准备'].str.strip(',')
            df['技术准备'] = df['技术准备'] + '\n' + df['行项目备注']
            df = df.drop(columns='出线方式_1')
            # 10. 调整列顺序
            cols = df.columns.tolist()
            col_to_move = '技术准备'
            idx = cols.index(col_to_move)
            cols.insert(2, cols.pop(idx))
            df = df[cols]
            df = df.sort_values(by='id')
            df = df.drop(columns='id')
            file_path = re.sub(r'\.xls$', '.xlsx', file_path)
            # 11. 保存输出文件
            output_file, _ = QFileDialog.getSaveFileName(
                self,
                "保存处理结果",
                file_path,
                "Excel Files (*.xlsx)"
            )
            if not output_file:
                return
            df.to_excel(output_file, index=False)
            QMessageBox.information(self, "完成", f"文件已保存至：{output_file}")
            os.startfile(output_file)
        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

    def handle_check_work_and_maintain_nameplate(self):
        # 1. 打开文件对话框
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择Excel文件",
            "",
            "Excel Files (*.xls *.xlsx)"
        )
        if not file_path:
            return  # 用户取消了选择
        try:
            # 2. 读取 Excel 文件
            df_check = pd.read_excel(file_path, dtype=str)
            df_check['id'] = range(1, len(df_check) + 1)  # del col
            df_check['物料长文本描述'] = df_check['物料长文本描述'].str.replace('5-50', '5~50')
            df_check['技术准备'] = df_check['技术准备'].str.replace('：', ':')
            df_check.insert(loc=0, column='bom错误', value='')
            map_position_check = {
                '顶部右出线': '顶右',
                '顶部左出线': '顶左',
                '顶部前出线': '顶前',
                '顶部后出线': '顶后',
                '左侧下出线': '左下',
                '左侧上出线': '左上',
                '右侧后出线': '右后',
                '右侧上出线': '右上',
                '右侧下出线': '右下',
                '左侧后出线': '左后',
                '': '顶右'
            }
            df_check['主接线盒位置及方向_del'] = df_check['主接线盒位置及方向'].fillna('顶部右出线').map(
                map_position_check).str.replace(
                '顶右', '')
            map_jydj = {
                '155(F)': 'F级',
                '180(H)': 'H级'
            }
            df_check['绝缘等级_del'] = df_check['绝缘等级'].fillna('155(F)').map(map_jydj).str.replace('F级', '')

            pattern = r'面漆:?([A-Z]+\d+)'
            df_check['面漆_del'] = df_check['技术准备'].str.extract(pattern).fillna('')
            checkcleaner = CheckCleaner(df_check)
            checkcleaner.check_and_clean('电压', '380')
            checkcleaner.check_and_clean('频率', '50')
            checkcleaner.check_and_clean('防护等级', 'IP55')
            checkcleaner.check_and_clean('冷却方式', 'IC411')
            checkcleaner.check_and_clean('轴承品牌', '国内')
            checkcleaner.check_and_clean('旋转方向', '双向')
            checkcleaner.check_and_clean('安装方式', '重置')
            checkcleaner.check_in_description('面漆')
            checkcleaner.check_in_description('电压')
            checkcleaner.check_in_description('频率')
            checkcleaner.check_in_description('绝缘等级')
            checkcleaner.check_in_description('防护等级')
            checkcleaner.check_in_description('冷却方式')
            checkcleaner.check_in_description('轴承品牌')
            checkcleaner.check_in_description('旋转方向')
            checkcleaner.check_in_description('安装方式')
            checkcleaner.check_in_description('主接线盒位置及方向')
            checkcleaner.check_normal_in_description('产品型号')
            checkcleaner.check_normal_in_description('功率')

            bom_columns = df_check.filter(like='_bom').columns.tolist()
            df_check['bom错误'] = df_check[bom_columns].sum(axis=1)
            df_check = df_check.loc[:, ~df_check.columns.str.contains('_bom')]

            df1_check = df_check[~df_check['产品型号'].str.contains('YB', na=False)].copy()  # 非
            df2_check = df_check[df_check['产品型号'].str.contains('YB', na=False)].copy()
            if not df1_check.empty:
                df1_check['出线方式_del'] = df1_check['出线方式'].fillna('葛兰头')
                map_df1_gl_check = {'其他': '',
                                    '葛兰头': '',
                                    '钢布': '钢布',
                                    '橡套电缆(喇叭口)': '喇叭口',
                                    '标准': '',
                                    '钢布式葛兰': ''
                                    }
                df1_check['出线方式_del'] = df1_check['出线方式_del'].map(map_df1_gl_check).fillna('')
                checkcleaner_df1 = CheckCleaner(df1_check)
                checkcleaner_df1.check_in_description('出线方式')
                checkcleaner_df1.check_and_clean('环境条件', '户内')
                checkcleaner_df1.check_in_description('环境条件')
                checkcleaner_df1.review(col='环境条件',col_standard_config='户内',col_non_standard_configs=['^.*?-.*?-\d[a-zA-Z]'])

                ffb_color_review_false = (~df1_check['技术准备'].str.contains('面漆:', na=False)) & (
                    ~df1_check['物料长文本描述'].str.contains('GE新油漆', na=False))
                if ffb_color_review_false.any():
                    df1_check.loc[ffb_color_review_false, 'bom错误'] = df1_check.loc[
                                                                           ffb_color_review_false, 'bom错误'] + ',面漆'

            if not df2_check.empty:
                df2_check['出线方式_del'] = df2_check['出线方式'].fillna('橡套')
                map_df2_gl_check = {
                    '钢布': '钢布',
                    '橡套电缆(喇叭口)': '橡套',
                    '标准': '橡套'
                }
                df2_check['出线方式_del'] = df2_check['出线方式_del'].map(map_df2_gl_check).fillna('')
                checkcleaner_df2 = CheckCleaner(df2_check)
                checkcleaner_df2.check_in_description('出线方式')
                map_ep_2_check = {'C5': '防腐',
                                  'F1': '防腐',
                                  'F2': '防腐',
                                  'GF2': '防腐',
                                  'GTHWF2': '防腐',
                                  'GWF1': '防腐',
                                  'GWF2': '防腐',
                                  'THF1': '防腐',
                                  'THWF1': '防腐',
                                  'THWF2': '防腐',
                                  'WF1': '防腐',
                                  'WF2': '防腐',
                                  'WTHF2': '防腐',
                                  'G': '',
                                  'GW': '',
                                  'TH': '',
                                  'W': '',
                                  '户内': ''
                                  }
                df2_check['环境条件_del'] = df2_check['环境条件'].fillna('户内').map(map_ep_2_check).fillna('')
                checkcleaner_df2.check_in_description('环境条件')

                fb_color_review_false = (~df2_check['技术准备'].str.contains('面漆:', na=False)) & (
                    ~df2_check['物料长文本描述'].str.contains('RAL5012', na=False))
                if fb_color_review_false.any():
                    df2_check.loc[fb_color_review_false, 'bom错误'] = df2_check.loc[
                                                                          fb_color_review_false, 'bom错误'] + ',面漆'

            df_check = pd.concat([df1_check, df2_check], join='outer', ignore_index=True)
            df_check['bom错误'] = df_check['bom错误'] + df_check['出线方式_bom'] + df_check['环境条件_bom']

            checkcleaner_pro = CheckCleaner(df_check)
            checkcleaner_pro.review(col='防护等级', col_standard_config='IP55',
                                    col_non_standard_configs=['IP65', 'IP66', 'IP56'])
            checkcleaner_pro.review(col='冷却方式', col_standard_config='IC411',
                                    col_non_standard_configs=['IC416', 'IC410', 'IC418'])
            checkcleaner_pro.review(col='轴承品牌', col_standard_config='国内',
                                    col_non_standard_configs=['SKF', 'NSK', '哈瓦洛'])
            checkcleaner_pro.review(col='主接线盒位置及方向', col_standard_config='顶部右出线',
                                    col_non_standard_configs=['顶左', '右下', '顶前', '顶后', '左下', '左上', '右上',
                                                              '左后', '右后'])
            checkcleaner_pro.review(col='安装方式', col_standard_config='B3',
                                    col_non_standard_configs=['B35', 'B34'])
            checkcleaner_pro.review(col='安装方式', col_standard_config='V1',
                                    col_non_standard_configs=['V15', 'V18'])

            df_check = df_check.loc[:, ~df_check.columns.str.contains('_bom') & ~df_check.columns.str.contains('_del')]

            df_check = df_check.sort_values(by='id')
            df_check = df_check.drop(columns='id')
            df_check['bom错误'] = df_check['bom错误'].str.strip(',')

            # 11. 保存输出文件
            self.display_dataframe(df_check, self.table)
            # 12. 提示完成
            QMessageBox.information(self, "完成", f"文件已显示")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

        try:
            engine = create_engine('mysql+pymysql://root:2001@localhost:3306/projectx')

            with engine.connect() as connection:
                query_1 = txt(f"SELECT * FROM in_ffb")
                df1_io = pd.read_sql(query_1, connection)
                query_2 = txt(f"SELECT * FROM in_fb")
                df2_io = pd.read_sql(query_2, connection)

            io_data_ffb = df1_io.set_index('in')['out']
            io_data_fb = df2_io.set_index('in')['out']

            # 2. 读取 Excel 文件
            df = pd.read_excel(file_path, dtype=str)
            df = df[['工作令号', '物料号码', '技术准备', '物料长文本描述',
                     '产品型号', '功率', '电压',
                     '频率', '防护等级', '绝缘等级',
                     '冷却方式', '环境条件', '防爆等级']]
            df.columns = ['出厂编码', '物料号', '技术准备', '中文描述',
                          '型号', '额定功率', '额定电压',
                          '频率', '防护等级', '绝缘等级',
                          '冷却方式', '防腐等级', '防爆等级']
            for column in df.columns:
                df[column] = df[column].str.strip()
            df['id'] = range(1, len(df) + 1)  # del col
            df['技术准备'] = df['技术准备'].str.replace('：', ':')
            df['技术准备'] = df['技术准备'].str.replace('M', 'm')
            df['防护等级'] = df['防护等级'].str.replace('IP', '', regex=False)
            df['冷却方式'] = df['冷却方式'].str.replace('IC', '', regex=False)

            mask = df['技术准备'].str.contains('铭牌打|位号:|牌号:|代号:', na=False)
            special_factory_codes = df.loc[mask, '出厂编码'].tolist()

            df1 = df[~df['型号'].str.contains('YB', na=False)].copy()  # 非防爆
            df1['size'] = df1['型号'].str.extract(r'-(\d+)')
            df2 = df[df['型号'].str.contains('YB', na=False)].copy()
            df2['size'] = df2['型号'].str.extract(r'(.*?-.*?\d+)')
            if not df1.empty:
                df1['param_cat'] = df1['额定功率'].astype(str) + '&' + df1['额定电压'].astype(str)
                series_nep = df1['param_cat'].map(data_xjj_ffb)
                df1.insert(2, '接法', series_nep)
                df1 = df1.drop(columns=['param_cat'])

                df1['in'] = df1['型号'].astype(str) + '&' + df1['额定功率'].astype(str) + '&' + df1['额定电压'].astype(
                    str)
                df1['out'] = df1['in'].map(io_data_ffb)
                df_split_nep = df1['out'].str.split('&', expand=True)
                df_split_nep.columns = ['电流', '转速', '效率', '功率因数', '重量', '标准编码', '噪声', '驱动端轴承',
                                        '非驱动轴承']
                df1 = pd.concat([df1, df_split_nep], axis=1)

                target_mask_zc = df1['型号'].str.contains('WEBP|YP', na=False)
                if target_mask_zc.any():
                    concatenated_zc = (
                            df1.loc[target_mask_zc, '型号'].astype(str) + '&' +
                            df1.loc[target_mask_zc, '额定功率'].astype(str) + '&' +
                            df1.loc[target_mask_zc, '额定电压'].astype(str) + '&' +
                            df1.loc[target_mask_zc, '冷却方式'].astype(str)
                    )
                    list_zc = concatenated_zc.map(model_zc).str.split('&', expand=True)
                    df1.loc[target_mask_zc, '驱动端轴承'] = list_zc[0]  # 第一列
                    df1.loc[target_mask_zc, '非驱动轴承'] = list_zc[1]  # 第二列
                else:
                    pass

                df1['铭牌料号'] = df1['size'].map(model_data_ffb['铭牌料号'])
                df1['打印模板'] = df1['size'].map(model_data_ffb['打印模板'])
            if not df2.empty:
                df2['防爆等级'] = df2['防爆等级'].fillna('')
                fb_grade = ~df2['防爆等级'].str.contains('T', na=False)
                special_fb_grade = df2.loc[fb_grade, '出厂编码'].tolist()
                if special_fb_grade:
                    QMessageBox.information(
                        self,
                        "注意",
                        f"需要补上防爆等级: {','.join(special_fb_grade)}"
                    )
                df2['param_cat'] = df2['额定功率'].astype(str) + '&' + df2['额定电压'].astype(str)
                series_ep = df2['param_cat'].map(data_xjj_fb)
                df2.insert(2, '接法', series_ep)
                df2 = df2.drop(columns=['param_cat'])

                df2['in'] = df2['型号'].astype(str) + '&' + df2['额定功率'].astype(str) + '&' + df2['额定电压'].astype(
                    str)
                df2['out'] = df2['in'].map(io_data_fb).str.replace('.0', '', regex=False)
                df_split_ep = df2['out'].str.split('&', expand=True)
                df_split_ep.columns = ['驱动端轴承', '非驱动轴承', '标准编码', '效率', '功率因数', '电流', '噪声',
                                       '转速', '重量']
                df2 = pd.concat([df2, df_split_ep], axis=1)

                df2['防爆等级_del'] = df2['防爆等级'].astype(str).str.replace(' ', '').apply(proof_grade)

                def get_cne_code(row, cne):
                    explosion_type = row['防爆等级_del']  # 从行中获取防爆等级
                    model = row['size']  # 从行中获取型号
                    if not explosion_type:
                        return ''  # 或者返回其他默认值
                    return cne[explosion_type].get(model, '')  # 查找编码

                df2['备用列8'] = df2.apply(get_cne_code, args=(cne,), axis=1)
                df2 = df2.drop(columns=['防爆等级_del'])

                df2['铭牌料号'] = df2['size'].map(model_fb_dict['铭牌料号'])
                df2['打印模板'] = df2['size'].map(model_fb_dict['打印模板'])

            df = pd.concat([df1, df2], join='outer', axis=0, ignore_index=True)

            columns_to_drop = ['in', 'out', 'size']
            df = df.drop(columns=columns_to_drop)

            df['驱动端轴承'] = df['驱动端轴承'].str.strip()
            df['非驱动轴承'] = df['非驱动轴承'].str.strip()

            df['标准编码'] = df['标准编码'].str.replace('  ', ' ')
            df['绝缘等级'] = df['绝缘等级'].fillna('155(F)')
            df['冷却方式'] = df['冷却方式'].fillna('411')
            df['防护等级'] = df['防护等级'].fillna('55')

            df.loc[df['中文描述'].str.contains('SKF', na=False), '驱动端轴承'] = 'SKF ' + df.loc[
                df['中文描述'].str.contains('SKF', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('NSK', na=False), '驱动端轴承'] = 'NSK ' + df.loc[
                df['中文描述'].str.contains('NSK', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('哈瓦洛', na=False), '驱动端轴承'] = '哈瓦洛 ' + df.loc[
                df['中文描述'].str.contains('哈瓦洛', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('FAG', na=False), '驱动端轴承'] = 'FAG ' + df.loc[
                df['中文描述'].str.contains('FAG', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('SKF', na=False), '非驱动轴承'] = 'SKF ' + df.loc[
                df['中文描述'].str.contains('SKF', na=False), '非驱动轴承']
            df.loc[df['中文描述'].str.contains('NSK', na=False), '非驱动轴承'] = 'NSK ' + df.loc[
                df['中文描述'].str.contains('NSK', na=False), '非驱动轴承']
            df.loc[df['中文描述'].str.contains('哈瓦洛', na=False), '非驱动轴承'] = '哈瓦洛 ' + df.loc[
                df['中文描述'].str.contains('哈瓦洛', na=False), '非驱动轴承']
            df.loc[df['中文描述'].str.contains('FAG', na=False), '非驱动轴承'] = 'FAG ' + df.loc[
                df['中文描述'].str.contains('FAG', na=False), '非驱动轴承']

            df.loc[df['中文描述'].str.contains('角接', na=False), '接法'] = '△'
            df.loc[df['中文描述'].str.contains('星接', na=False), '接法'] = 'Y'
            #
            df['技术准备'] = df['技术准备'].str.replace('，', ',', regex=False)
            df['技术准备'] = df['技术准备'].str.replace('：', ':', regex=False)
            df['技术准备'] = df['技术准备'].str.replace('M', 'm', regex=False)
            df['环境温度'] = df['技术准备'].str.extract(r'(环境温度:[^℃]+℃)')
            df['海拔高度'] = df['技术准备'].str.extract(r'(海拔高度:[^m]+m)')

            df['噪声'] = df['噪声'].str.replace(' ', '')
            df['防腐等级'] = df['防腐等级'].str.replace('户内', '')

            df['工作制'] = '1'
            df['服务系数SF'] = '1'
            df['编码规则'] = ''

            df = df.sort_values(by='id')
            df = df.drop(columns=['技术准备', 'id'])

            file_path = re.sub(r'\.xls$', '.xlsx', file_path)

            # 11. 保存输出文件
            output_file, _ = QFileDialog.getSaveFileName(
                self,
                "保存处理结果",
                file_path,
                "Excel Files (*.xlsx)"
            )
            if not output_file:
                return
            df.to_excel(output_file, index=False)
            self.display_dataframe(df, self.table02)

            if special_factory_codes:
                QMessageBox.information(
                    self,
                    "注意",
                    f"以下出厂编码的技术准备中包含'铭牌打','位号:','牌号:','代号:': \n{','.join(special_factory_codes)}"
                )
            # 12. 提示完成
            QMessageBox.information(self, "完成", f"文件已保存至：{output_file}")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

    def total_table(self):
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择Excel文件",
            "",
            "Excel Files (*.xls *.xlsx)"
        )
        if not file_path:
            return  # 用户取消了选择

        try:
            # 2. 读取 Excel 文件
            df = pd.read_excel(file_path, dtype=str)
            df = df[['流程编号', '工作令号', '物料编码', '物料长描述',
                     '产品型号', '功率', '电压等级',
                     '频率', '安装方式', '绝缘等级', '防护等级', '出线方式',
                     '环境条件', '冷却方式', '行项目备注',
                     '防爆等级', '环境温度', '主接线盒位置方向', '旋转方向', '轴承品牌', '海拔高度']]  # 列筛选
            df.columns = ['流程编号', '工作令号', '物料号码', '物料长文本描述',
                          '产品型号', '功率', '电压',
                          '频率', '安装方式', '绝缘等级', '防护等级', '出线方式',
                          '环境条件', '冷却方式', '技术准备',
                          '防爆等级', '环境温度', '主接线盒位置及方向', '旋转方向', '轴承品牌', '海拔高度']
            df = df.dropna(subset=['工作令号', '物料号码', '物料长文本描述', '产品型号'], how='any')
            df = df[~df['物料号码'].str.contains(r'^[0-9]')]
            df['旋转方向'] = df['旋转方向'].str.replace('标准', '双向')
            text_1 = self.lineEdit.text()
            try:
                engine = create_engine(text_1)

                with engine.connect() as connection:
                    query = txt(f"SELECT 工作令号 FROM fbz")
                    existing_ids = pd.read_sql(query, connection)['工作令号'].tolist()
                df = df[~df['工作令号'].isin(existing_ids)]
                output_file, _ = QFileDialog.getSaveFileName(
                    self,
                    "保存处理结果",
                    file_path,
                    "Excel Files (*.xlsx)"
                )
                if not output_file:
                    return
                df.to_excel(output_file, index=False)
            except Exception as e:
                QMessageBox.critical(self, "数据库错误", f"数据库操作失败：{str(e)}")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

    def old_item(self):
        # 1. 打开文件对话框
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择Excel文件",
            "",
            "Excel Files (*.xls *.xlsx)"
        )
        if not file_path:
            return  # 用户取消了选择
        try:
            # 2. 读取 Excel 文件
            df_check = pd.read_excel(file_path, dtype=str)
            df_check['id'] = range(1, len(df_check) + 1)  # del col
            df_check['物料长文本描述'] = df_check['物料长文本描述'].str.replace('5-50', '5~50')
            df_check['技术准备'] = df_check['技术准备'].str.replace('：', ':')
            df_check.insert(loc=0, column='bom错误', value='')
            map_position_check = {
                '顶部右出线': '顶右',
                '顶部左出线': '顶左',
                '顶部前出线': '顶前',
                '顶部后出线': '顶后',
                '左侧下出线': '左下',
                '左侧上出线': '左上',
                '右侧后出线': '右后',
                '右侧上出线': '右上',
                '右侧下出线': '右下',
                '左侧后出线': '左后',
                '': '顶右'
            }
            df_check['主接线盒位置及方向_del'] = df_check['主接线盒位置及方向'].fillna('顶部右出线').map(
                map_position_check).str.replace(
                '顶右', '')
            map_jydj = {
                '155(F)': 'F级',
                '180(H)': 'H级'
            }
            df_check['绝缘等级_del'] = df_check['绝缘等级'].fillna('155(F)').map(map_jydj).str.replace('F级', '')

            pattern = r'面漆:?([A-Z]+\d+)'
            df_check['面漆_del'] = df_check['技术准备'].str.extract(pattern).fillna('')
            checkcleaner = CheckCleaner(df_check)
            checkcleaner.check_and_clean('电压', '380')
            checkcleaner.check_and_clean('频率', '50')
            checkcleaner.check_and_clean('防护等级', 'IP55')
            checkcleaner.check_and_clean('冷却方式', 'IC411')
            checkcleaner.check_and_clean('轴承品牌', '国内')
            checkcleaner.check_and_clean('旋转方向', '双向')
            checkcleaner.check_and_clean('安装方式', '重置')
            checkcleaner.check_in_description('面漆')
            checkcleaner.check_in_description('电压')
            checkcleaner.check_in_description('频率')
            checkcleaner.check_in_description('绝缘等级')
            checkcleaner.check_in_description('防护等级')
            checkcleaner.check_in_description('冷却方式')
            checkcleaner.check_in_description('轴承品牌')
            checkcleaner.check_in_description('旋转方向')
            checkcleaner.check_in_description('安装方式')
            checkcleaner.check_in_description('主接线盒位置及方向')
            checkcleaner.check_normal_in_description('产品型号')
            checkcleaner.check_normal_in_description('功率')

            bom_columns = df_check.filter(like='_bom').columns.tolist()
            df_check['bom错误'] = df_check[bom_columns].sum(axis=1)
            df_check = df_check.loc[:, ~df_check.columns.str.contains('_bom')]

            df1_check = df_check[~df_check['产品型号'].str.contains('YB', na=False)].copy()  # 非
            df2_check = df_check[df_check['产品型号'].str.contains('YB', na=False)].copy()
            if not df1_check.empty:
                df1_check['出线方式_del'] = df1_check['出线方式'].fillna('葛兰头')
                map_df1_gl_check = {'其他': '',
                                    '葛兰头': '',
                                    '钢布': '钢布',
                                    '橡套电缆(喇叭口)': '喇叭口',
                                    '标准': '',
                                    '钢布式葛兰': ''
                                    }
                df1_check['出线方式_del'] = df1_check['出线方式_del'].map(map_df1_gl_check).fillna('')
                checkcleaner_df1 = CheckCleaner(df1_check)
                checkcleaner_df1.check_in_description('出线方式')
                checkcleaner_df1.check_and_clean('环境条件', '户内')
                checkcleaner_df1.check_in_description('环境条件')

            if not df2_check.empty:
                df2_check['出线方式_del'] = df2_check['出线方式'].fillna('橡套')
                map_df2_gl_check = {
                    '钢布': '钢布',
                    '橡套电缆(喇叭口)': '橡套',
                    '标准': '橡套'
                }
                df2_check['出线方式_del'] = df2_check['出线方式_del'].map(map_df2_gl_check).fillna('')
                checkcleaner_df2 = CheckCleaner(df2_check)
                checkcleaner_df2.check_in_description('出线方式')
                map_ep_2_check = {'C5': '防腐',
                                  'F1': '防腐',
                                  'F2': '防腐',
                                  'GF2': '防腐',
                                  'GTHWF2': '防腐',
                                  'GWF1': '防腐',
                                  'GWF2': '防腐',
                                  'THF1': '防腐',
                                  'THWF1': '防腐',
                                  'THWF2': '防腐',
                                  'WF1': '防腐',
                                  'WF2': '防腐',
                                  'WTHF2': '防腐',
                                  'G': '',
                                  'GW': '',
                                  'TH': '',
                                  'W': '',
                                  '户内': ''
                                  }
                df2_check['环境条件_del'] = df2_check['环境条件'].fillna('户内').map(map_ep_2_check).fillna('')
                checkcleaner_df2.check_in_description('环境条件')
            df_check = pd.concat([df1_check, df2_check], join='outer', ignore_index=True)
            df_check['bom错误'] = df_check['bom错误'] + df_check['出线方式_bom'] + df_check['环境条件_bom']

            checkcleaner_pro = CheckCleaner(df_check)
            checkcleaner_pro.review(col='防护等级', col_standard_config='IP55',
                                    col_non_standard_configs=['IP65', 'IP66', 'IP56'])
            checkcleaner_pro.review(col='冷却方式', col_standard_config='IC411',
                                    col_non_standard_configs=['IC416', 'IC410', 'IC418'])
            checkcleaner_pro.review(col='轴承品牌', col_standard_config='国内',
                                    col_non_standard_configs=['SKF', 'NSK', '哈瓦洛'])
            checkcleaner_pro.review(col='主接线盒位置及方向', col_standard_config='顶部右出线',
                                    col_non_standard_configs=['顶左', '右下', '顶前', '顶后', '左下', '左上', '右上',
                                                              '左后', '右后'])

            df_check = df_check.loc[:, ~df_check.columns.str.contains('_bom') & ~df_check.columns.str.contains('_del')]

            df_check = df_check.sort_values(by='id')
            df_check = df_check.drop(columns='id')
            df_check['bom错误'] = df_check['bom错误'].str.strip(',')

            file_path = re.sub(r'\.xls$', '.xlsx', file_path)

            # 11. 保存输出文件
            output_file, _ = QFileDialog.getSaveFileName(
                self,
                "保存处理结果",
                file_path,
                "Excel Files (*.xlsx)"
            )
            if not output_file:
                return
            df_check.to_excel(output_file, index=False)

            # 11. 保存输出文件
            self.display_dataframe(df_check, self.table)
            # 12. 提示完成
            QMessageBox.information(self, "完成", f"文件已显示")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

        try:
            engine = create_engine('mysql+pymysql://root:2001@localhost:3306/projectx')

            with engine.connect() as connection:
                query_1 = txt(f"SELECT * FROM in_ffb")
                df1_io = pd.read_sql(query_1, connection)
                query_2 = txt(f"SELECT * FROM in_fb")
                df2_io = pd.read_sql(query_2, connection)

            io_data_ffb = df1_io.set_index('in')['out']
            io_data_fb = df2_io.set_index('in')['out']

            # 2. 读取 Excel 文件
            df = pd.read_excel(file_path, dtype=str)
            df = df[['工作令号', '物料号码', '技术准备', '物料长文本描述',
                     '产品型号', '功率', '电压',
                     '频率', '防护等级', '绝缘等级',
                     '冷却方式', '环境条件', '防爆等级']]
            df.columns = ['出厂编码', '物料号', '技术准备', '中文描述',
                          '型号', '额定功率', '额定电压',
                          '频率', '防护等级', '绝缘等级',
                          '冷却方式', '防腐等级', '防爆等级']
            for column in df.columns:
                df[column] = df[column].str.strip()
            df['id'] = range(1, len(df) + 1)  # del col
            df['技术准备'] = df['技术准备'].str.replace('：', ':')
            df['技术准备'] = df['技术准备'].str.replace('M', 'm')
            df['防护等级'] = df['防护等级'].str.replace('IP', '', regex=False)
            df['冷却方式'] = df['冷却方式'].str.replace('IC', '', regex=False)

            mask = df['技术准备'].str.contains('铭牌打|位号:|牌号:|代号:', na=False)
            special_factory_codes = df.loc[mask, '出厂编码'].tolist()

            df1 = df[~df['型号'].str.contains('YB', na=False)].copy()  # 非防爆
            df1['size'] = df1['型号'].str.extract(r'-(\d+)')
            df2 = df[df['型号'].str.contains('YB', na=False)].copy()
            df2['size'] = df2['型号'].str.extract(r'(.*?-.*?\d+)')
            if not df1.empty:
                df1['param_cat'] = df1['额定功率'].astype(str) + '&' + df1['额定电压'].astype(str)
                series_nep = df1['param_cat'].map(data_xjj_ffb)
                df1.insert(2, '接法', series_nep)
                df1 = df1.drop(columns=['param_cat'])

                df1['in'] = df1['型号'].astype(str) + '&' + df1['额定功率'].astype(str) + '&' + df1['额定电压'].astype(
                    str)
                df1['out'] = df1['in'].map(io_data_ffb)
                df_split_nep = df1['out'].str.split('&', expand=True)
                df_split_nep.columns = ['电流', '转速', '效率', '功率因数', '重量', '标准编码', '噪声', '驱动端轴承',
                                        '非驱动轴承']
                df1 = pd.concat([df1, df_split_nep], axis=1)

                target_mask_zc = df1['型号'].str.contains('WEBP|YP', na=False)
                if target_mask_zc.any():
                    concatenated_zc = (
                            df1.loc[target_mask_zc, '型号'].astype(str) + '&' +
                            df1.loc[target_mask_zc, '额定功率'].astype(str) + '&' +
                            df1.loc[target_mask_zc, '额定电压'].astype(str) + '&' +
                            df1.loc[target_mask_zc, '冷却方式'].astype(str)
                    )
                    list_zc = concatenated_zc.map(model_zc).str.split('&', expand=True)
                    df1.loc[target_mask_zc, '驱动端轴承'] = list_zc[0]  # 第一列
                    df1.loc[target_mask_zc, '非驱动轴承'] = list_zc[1]  # 第二列
                else:
                    pass

                df1['铭牌料号'] = df1['size'].map(model_data_ffb['铭牌料号'])
                df1['打印模板'] = df1['size'].map(model_data_ffb['打印模板'])
            if not df2.empty:
                df2['防爆等级'] = df2['防爆等级'].fillna('')
                fb_grade = ~df2['防爆等级'].str.contains('T', na=False)
                special_fb_grade = df2.loc[fb_grade, '出厂编码'].tolist()
                if special_fb_grade:
                    QMessageBox.information(
                        self,
                        "注意",
                        f"需要补上防爆等级: {','.join(special_fb_grade)}"
                    )
                df2['param_cat'] = df2['额定功率'].astype(str) + '&' + df2['额定电压'].astype(str)
                series_ep = df2['param_cat'].map(data_xjj_fb)
                df2.insert(2, '接法', series_ep)
                df2 = df2.drop(columns=['param_cat'])

                df2['in'] = df2['型号'].astype(str) + '&' + df2['额定功率'].astype(str) + '&' + df2['额定电压'].astype(
                    str)
                df2['out'] = df2['in'].map(io_data_fb).str.replace('.0', '', regex=False)
                df_split_ep = df2['out'].str.split('&', expand=True)
                df_split_ep.columns = ['驱动端轴承', '非驱动轴承', '标准编码', '效率', '功率因数', '电流', '噪声',
                                       '转速', '重量']
                df2 = pd.concat([df2, df_split_ep], axis=1)

                df2['防爆等级_del'] = df2['防爆等级'].astype(str).str.replace(' ', '').apply(proof_grade)

                def get_cne_code(row, cne):
                    explosion_type = row['防爆等级_del']  # 从行中获取防爆等级
                    model = row['size']  # 从行中获取型号
                    if not explosion_type:
                        return ''  # 或者返回其他默认值
                    return cne[explosion_type].get(model, '')  # 查找编码

                df2['备用列8'] = df2.apply(get_cne_code, args=(cne,), axis=1)
                df2 = df2.drop(columns=['防爆等级_del'])

                df2['铭牌料号'] = df2['size'].map(model_fb_dict['铭牌料号'])
                df2['打印模板'] = df2['size'].map(model_fb_dict['打印模板'])

            df = pd.concat([df1, df2], join='outer', axis=0, ignore_index=True)

            columns_to_drop = ['in', 'out', 'size']
            df = df.drop(columns=columns_to_drop)

            df['驱动端轴承'] = df['驱动端轴承'].str.strip()
            df['非驱动轴承'] = df['非驱动轴承'].str.strip()

            df['标准编码'] = df['标准编码'].str.replace('  ', ' ')
            df['绝缘等级'] = df['绝缘等级'].fillna('155(F)')
            df['冷却方式'] = df['冷却方式'].fillna('411')
            df['防护等级'] = df['防护等级'].fillna('55')

            df.loc[df['中文描述'].str.contains('SKF', na=False), '驱动端轴承'] = 'SKF ' + df.loc[
                df['中文描述'].str.contains('SKF', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('NSK', na=False), '驱动端轴承'] = 'NSK ' + df.loc[
                df['中文描述'].str.contains('NSK', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('哈瓦洛', na=False), '驱动端轴承'] = '哈瓦洛 ' + df.loc[
                df['中文描述'].str.contains('哈瓦洛', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('FAG', na=False), '驱动端轴承'] = 'FAG ' + df.loc[
                df['中文描述'].str.contains('FAG', na=False), '驱动端轴承']
            df.loc[df['中文描述'].str.contains('SKF', na=False), '非驱动轴承'] = 'SKF ' + df.loc[
                df['中文描述'].str.contains('SKF', na=False), '非驱动轴承']
            df.loc[df['中文描述'].str.contains('NSK', na=False), '非驱动轴承'] = 'NSK ' + df.loc[
                df['中文描述'].str.contains('NSK', na=False), '非驱动轴承']
            df.loc[df['中文描述'].str.contains('哈瓦洛', na=False), '非驱动轴承'] = '哈瓦洛 ' + df.loc[
                df['中文描述'].str.contains('哈瓦洛', na=False), '非驱动轴承']
            df.loc[df['中文描述'].str.contains('FAG', na=False), '非驱动轴承'] = 'FAG ' + df.loc[
                df['中文描述'].str.contains('FAG', na=False), '非驱动轴承']

            df.loc[df['中文描述'].str.contains('角接', na=False), '接法'] = '△'
            df.loc[df['中文描述'].str.contains('星接', na=False), '接法'] = 'Y'
            #
            df['技术准备'] = df['技术准备'].str.replace('，', ',', regex=False)
            df['技术准备'] = df['技术准备'].str.replace('：', ':', regex=False)
            df['技术准备'] = df['技术准备'].str.replace('M', 'm', regex=False)
            df['环境温度'] = df['技术准备'].str.extract(r'(环境温度:[^℃]+℃)')
            df['海拔高度'] = df['技术准备'].str.extract(r'(海拔高度:[^m]+m)')

            df['噪声'] = df['噪声'].str.replace(' ', '')
            df['防腐等级'] = df['防腐等级'].str.replace('户内', '')

            df['工作制'] = '1'
            df['服务系数SF'] = '1'
            df['编码规则'] = ''

            df = df.sort_values(by='id')
            df = df.drop(columns=['技术准备', 'id'])

            file_path = re.sub(r'\.xls$', '.xlsx', file_path)


            output_file, _ = QFileDialog.getSaveFileName(
                self,
                "保存处理结果",
                file_path,
                "Excel Files (*.xlsx)"
            )
            if not output_file:
                return
            df.to_excel(output_file, index=False)
            self.display_dataframe(df, self.table02)

            if special_factory_codes:
                QMessageBox.information(
                    self,
                    "注意",
                    f"以下出厂编码的技术准备中包含'铭牌打','位号:','牌号:','代号:': \n{','.join(special_factory_codes)}"
                )

            QMessageBox.information(self, "完成", f"文件已保存至：{output_file}")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

    def to_mysql(self):
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择Excel文件",
            "",
            "Excel Files (*.xls *.xlsx)"
        )
        if not file_path:
            return  # 用户取消了选择
        try:
            df = pd.read_excel(file_path, dtype=str)
            # 创建数据库引擎
            text_1 = self.lineEdit.text()
            engine = create_engine(text_1)

            # 将 DataFrame 写入数据库
            df.to_sql('fbz', con=engine, if_exists='append', index=False)

        except SQLAlchemyError as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

    def query(self):
        text_1 = self.lineEdit.text()
        text_2 = self.lineEdit_2.text()
        try:
            engine = create_engine(text_1)

            with engine.connect() as connection:
                query = txt(text_2)
                df = pd.read_sql(query, connection)
            self.display_dataframe(df, self.table)

        except Exception as e:
            QMessageBox.critical(self, "数据库错误", f"数据库操作失败：{str(e)}")

    def non_greedy_recommender(self):
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择Excel文件",
            "",
            "Excel Files (*.xls *.xlsx)"
        )
        if not file_path:
            return

        try:

            df = pd.read_excel(file_path, dtype=str)
            df = df[['产品型号', '技术准备', '环境条件']]
            df['id'] = range(1, len(df) + 1)  # del col

            df['订单'] = df['技术准备'].apply(tptp)
            df['订单'] = df['订单'].str.replace('，', ',')
            df['订单'] = df['订单'].str.strip(' ,')
            df['订单'] = df['订单'].str.replace('：', ':')

            df['订单'] = df['产品型号'] + ',' + df['订单']
            df1 = df[~df['产品型号'].str.contains('YB', na=False)].copy()  # 非防爆
            df2 = df[df['产品型号'].str.contains('YB', na=False)].copy()
            if not df1.empty:
                df1['订单'] = df1['订单'].apply(sort_comma_elements_df1)
            if not df2.empty:
                df2['环境条件_2'] = df2.apply(assign_b_column, axis=1)
                df2['订单'] = df2.apply(sort_comma_elements_df2, axis=1)
                df2 = df2.drop(columns=['环境条件_2'])
            df = pd.concat([df1, df2], join='outer', axis=0, ignore_index=True)
            df = df.sort_values(by='id')

            df = df[['订单']]
            df['订单'] = df['订单'].str.strip(',')

            engine = create_engine('mysql+pymysql://root:2001@localhost:3306/projectx')

            with engine.connect() as connection:
                query = txt(f"SELECT * FROM hub")
                df_hub = pd.read_sql(query, connection)

            map_lh = df_hub.set_index('技术准备')['料号']
            map_ms = df_hub.set_index('技术准备')['描述']

            df['料号'] = df['订单'].map(map_lh)
            df['描述'] = df['订单'].map(map_ms)
            self.display_dataframe(df, self.table02)

            QMessageBox.information(self, "完成", f"推荐文件就绪")

        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")

    def greedy_recommender(self):
        file_path, _ = QFileDialog.getOpenFileName(
            self,
            "选择Excel文件",
            "",
            "Excel Files (*.xls *.xlsx)"
        )
        if not file_path:
            return  # 用户取消了选择
        try:
            # 2. 读取 Excel 文件
            df = pd.read_excel(file_path, dtype=str)
            df = df[['产品型号', '技术准备', '环境条件']]
            df['id'] = range(1, len(df) + 1)  # del col

            df['订单'] = df['技术准备'].apply(tptp)
            df['订单'] = df['订单'].str.replace('，', ',')
            df['订单'] = df['订单'].str.strip(' ,')
            df['订单'] = df['订单'].str.replace('：', ':')

            df['订单'] = df['产品型号'] + ',' + df['订单']
            df1 = df[~df['产品型号'].str.contains('YB', na=False)].copy()  # 非防爆
            df2 = df[df['产品型号'].str.contains('YB', na=False)].copy()
            if not df2.empty:
                df2['环境条件_2'] = df2.apply(assign_b_column, axis=1)
                df2['订单'] = df2.apply(order_clear_df2, axis=1)
                df2 = df2.drop(columns=['环境条件_2'])
            df = pd.concat([df1, df2], join='outer', axis=0, ignore_index=True)
            df = df.sort_values(by='id')

            df['订单_const'] = df['产品型号'].str.extract(r'^([^-]*-\d+)')
            df['订单_const'] = df['订单_const'].str.replace('YE', 'WE')
            df['订单_const'] = df['订单_const'].str.replace('YP', 'WEBP3')
            df = df[['订单', '订单_const']]

            df['订单'] = df['订单'].str.strip(',')

            engine = create_engine('mysql+pymysql://root:2001@localhost:3306/projectx')

            with engine.connect() as connection:
                query = txt(f"SELECT * FROM hub")
                df_hub = pd.read_sql(query, connection)

            hub_list = df_hub['技术准备'].str.split(',')
            order_list = df['订单'].str.split(',')
            results = []

            for order_idx, order_tags in order_list.items():
                best_score = 0
                best_hub_idx = None
                order_const = df.loc[order_idx, '订单_const']

                for hub_idx, hub_tags in hub_list.items():
                    hub_const = df_hub.loc[hub_idx, 'const']
                    if str(order_const) != str(hub_const):
                        continue
                    score = jaccard_similarity(order_tags, hub_tags)
                    if score > best_score:
                        best_score = score
                        best_hub_idx = hub_idx
                recommended_tec = df_hub.loc[best_hub_idx, '技术准备'] if best_hub_idx is not None else None
                recommended_num = df_hub.loc[best_hub_idx, '料号'] if best_hub_idx is not None else None
                recommended_des = df_hub.loc[best_hub_idx, '描述'] if best_hub_idx is not None else None

                results.append({
                    '匹配分数': best_score,
                    '技术准备': recommended_tec,
                    '推荐料号': recommended_num,
                    '推荐描述': recommended_des
                })


            result_df = pd.DataFrame(results)
            self.display_dataframe(result_df, self.table02)

            QMessageBox.information(self, "完成", f"推荐文件就绪")

        except Exception as e:
            QMessageBox.critical(self, "错误", f"发生错误：{str(e)}")


if __name__ == "__main__":
    app = QApplication(sys.argv)
    win = Window()
    win.show()
    sys.exit(app.exec_())
